package com.bw.dao;

import com.bw.pojo.Phone;
import com.bw.util.JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/***************************
 FileAction:

 CreateTime:2022-05-10 8:30
 Author:Aaking-aline
 ****************************/
public class PhoneDao {
    // 这是查询全部手机信息的方法
    public List<Phone> selectAllPhone() throws SQLException {
        String sql = "select * from phone";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        List<Phone> list = new ArrayList<>();

        while(rs.next()){
            Phone phone = new Phone(rs.getInt(1),
                    rs.getString(2),
                    rs.getDouble(3),
                    rs.getInt(4),
                    rs.getString(5),
                    rs.getString(6),
                    rs.getString(7));
            list.add(phone);
        }
        return list;
    }
    // 这是分页查询方法
    public List<Phone> selectPhoneByLimit(String page) throws SQLException {
        int index = (Integer.parseInt(page) - 1) * 3;

        String sql = "select * from phone limit ?,3";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,index);

        ResultSet rs = ps.executeQuery();

        List<Phone> list = new ArrayList<>();

        while(rs.next()){
            Phone phone = new Phone(rs.getInt(1),
                    rs.getString(2),
                    rs.getDouble(3),
                    rs.getInt(4),
                    rs.getString(5),
                    rs.getString(6),
                    rs.getString(7));
            list.add(phone);
        }
        return list;
    }
    // 这是查询全部手机品牌名方法
    public List<String> selectAllPhoneName() throws SQLException {
        String sql = "select phone_name from phone";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        List<String> phone_names = new ArrayList<>();

        while(rs.next()){
            phone_names.add(rs.getString(1));
        }
        return phone_names;
    }
    // 这是查询全部手机型号方法
    public List<String> selectAllPhoneModel() throws SQLException {
        String sql = "select phone_model from phone";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        List<String> phone_models = new ArrayList<>();

        while(rs.next()){
            phone_models.add(rs.getString(1));
        }
        return phone_models;
    }

    public int selectPageNum() throws SQLException {
        String sql = "select count(*) from phone"; // 写查询共有多少条数据的 sql语句

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        if(rs.next()){
            return (int)Math.ceil(rs.getInt(1) / 3.0);
        }
        return 0;
    }


    public void insertPhone(Phone phone) throws SQLException {
        String sql = "insert into phone values(null,?,?,?,?,?,?)";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,phone.getPhone_name());
        ps.setObject(2,phone.getPhone_price());
        ps.setObject(3,phone.getPhone_stock());
        ps.setObject(4,phone.getPhone_producer());
        ps.setObject(5,phone.getPhone_model());
        ps.setObject(6,phone.getMake_date());

        ps.executeUpdate();
    }

    public void deletePhone(String id) throws SQLException {
        String sql = "delete from phone where id in(" + id + ")"; // id = 1,2,3   4
        //String sql = "delete from phone where id in(1,2,3)";
        //String sql = "delete from phone where id in(5)";
        Connection conn = JDBC.getConnection();
        PreparedStatement ps = conn.prepareStatement(sql);

        ps.executeUpdate();
    }

    public void updatePhone(Phone phone) throws SQLException {
        String sql = "update phone set phone_name = ?,phone_price = ?,phone_stock = ?,phone_producer = ?,phone_model = ?,make_date = ? where id = ?";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,phone.getPhone_name());
        ps.setObject(2,phone.getPhone_price());
        ps.setObject(3,phone.getPhone_stock());
        ps.setObject(4,phone.getPhone_producer());
        ps.setObject(5,phone.getPhone_model());
        ps.setObject(6,phone.getMake_date());
        ps.setObject(7,phone.getId());

        ps.executeUpdate();

    }

    public List<Phone> selectPhoneByPhoneNameAndPhoneModel(String phone_name, String phone_model) throws SQLException {
        String sql = "select * from phone where phone_name = ? and phone_model = ?";

        Connection conn = JDBC.getConnection();

        PreparedStatement ps = conn.prepareStatement(sql);

        ps.setObject(1,phone_name);
        ps.setObject(2,phone_model);


        ResultSet rs = ps.executeQuery();

        List<Phone> list = new ArrayList<>();

        while(rs.next()){
            Phone phone = new Phone(rs.getInt(1),
                    rs.getString(2),
                    rs.getDouble(3),
                    rs.getInt(4),
                    rs.getString(5),
                    rs.getString(6),
                    rs.getString(7));
            list.add(phone);
        }
        return list;
    }
}
